﻿using System;
using System.Collections.Generic;
using System.Data;

using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client; 
//using Oracle.DataAccess.Types;

using LMD.Constant;
using LMD.Lib.DataClass;
using LMD.Lib.Oracle;

namespace DBQuery
{
    /// <summary>
    /// Summary description for CompanyTable
    /// </summary>
    public static class CompanyTable
    {
        public static void UpdateCustomData(CompanyProfileData cpdToUpdate)
        {
            Cached.CompanyTable ccCompany = new Cached.CompanyTable();
            CompanyProfileData cpdExisting = new CompanyProfileData();
            cpdExisting = ccCompany.GetCompanyProfileData(cpdToUpdate.ID, true);

            Dictionary<String, String> dicNew = cpdToUpdate.GetCustomDataDictionary();
            Dictionary<String, String> dicExisting = cpdExisting.GetCustomDataDictionary();

            List<String> lsChanged = new List<String>();
            String sxInfoName = String.Empty;
            Cached.CustomerInfoTypeTable ccInfo = new Cached.CustomerInfoTypeTable();

            DBLib dbl = new DBLib();
            dbl.BeginTransaction();
            try
            {
                foreach (KeyValuePair<String, String> item in dicNew)
                {

                    int iInfoType = Convert.ToInt32(item.Key);
                    sxInfoName = ccInfo.GetCustomerInfoTypeName(iInfoType);

                    if (dicExisting.ContainsKey(item.Key))
                    {
                        // UPDATE
                        if (String.IsNullOrEmpty(item.Value))
                        {
                            DeleteCustomerInfo(dbl, cpdToUpdate.ID, iInfoType);
                            lsChanged.Add("Deleted " + sxInfoName + " data");
                        }
                        else
                        {
                            UpdateCustomerInfo(dbl, cpdToUpdate.ID, iInfoType, item.Value);
                            lsChanged.Add("Updated " + sxInfoName + ": " + item.Value);
                        }

                        dicExisting.Remove(item.Key);
                    }
                    else
                    {
                        // INSERT
                        InsertCustomerInfo(dbl, cpdToUpdate.ID, iInfoType, item.Value);
                        lsChanged.Add("Added " + sxInfoName + ": " + item.Value);

                        String x = "1";
                        int y = int.Parse(x);
                    }
                }

                foreach (KeyValuePair<String, String> item in dicExisting)
                {
                    // DELETE YG SUDAH TIDAK ADA LAGI
                    int iInfoType = Convert.ToInt32(item.Key);
                    DeleteCustomerInfo(dbl, cpdToUpdate.ID, iInfoType);
                    sxInfoName = ccInfo.GetCustomerInfoTypeName(iInfoType);
                    lsChanged.Add("Deleted " + sxInfoName + " data");
                }

                CreateNewsFeed(dbl, cpdToUpdate, lsChanged);

            }
            catch (Exception ex)
            {
                dbl.EndTransaction(false);
                throw ex;
            }
            dbl.EndTransaction(true);
        }

        private static void CreateNewsFeed(DBLib dbl, CompanyProfileData cpd, List<String> lsChanged)
        {
            lsChanged.Sort();

            NewsFeedData nfd = new NewsFeedData();
            nfd.iCustomerId = cpd.ID;
            nfd.iSourceId = (int)NewsFeedSourceType.ICP;
            nfd.sxUserId_Poster = ICPUser.ID;
            nfd.sxTitle = "Company Profile Data has been updated!";
            foreach (String item in lsChanged)
            {
                nfd.sxContent += "<br/>" + item;
            }

            if (nfd.sxContent.Length > 255)
            {
                nfd.sxContent = nfd.sxContent.Substring(0, 255);
            }

            DBQuery.NewsFeedTable.SaveNewsFeed(dbl, nfd);
        }

        public static int InsertCustomerInfo(int iCustomerId, int iInfoTypeId, String sxValue)
        {
            DBLib dbl = new DBLib();
            int iReturn = InsertCustomerInfo(dbl, iCustomerId, iInfoTypeId, sxValue);
            
            return iReturn;
        }
        public static int InsertCustomerInfo(DBLib dbl, int iCustomerId, int iInfoTypeId, String sxValue)
        {
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "out_new_id";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Direction = ParameterDirection.Output;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_VALUE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = sxValue;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CUSTOMER_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = iCustomerId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_P_CUSTOMER_INFO_TYPE_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = iInfoTypeId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CREATE_BY";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

            String sxQuery = "PR_INSERT_T_CUSTOMER_INFO";

            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());

            return Convert.ToInt32(lsParams[0].Value);
        }

        public static void UpdateCustomerInfo(int iCustomerId, int iInfoTypeId, String sxValue)
        {
            DBLib dbl = new DBLib();
            UpdateCustomerInfo(dbl, iCustomerId, iInfoTypeId, sxValue);
            
        }
        public static void UpdateCustomerInfo(DBLib dbl, int iCustomerId, int iInfoTypeId, String sxValue)
        {
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_VALUE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = sxValue;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CUSTOMER_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = iCustomerId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_P_CUSTOMER_INFO_TYPE_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = iInfoTypeId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_UPDATE_BY";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

            String sxQuery = "PR_UPDATE_T_CUSTOMER_INFO";

            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
        }

        public static void DeleteCustomerInfo(int iCustomerId, int iInfoTypeId)
        {
            DBLib dbl = new DBLib();
            DeleteCustomerInfo(dbl, iCustomerId, iInfoTypeId);
            
        }
        public static void DeleteCustomerInfo(DBLib dbl, int iCustomerId, int iInfoTypeId)
        {
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CUSTOMER_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = iCustomerId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_P_CUSTOMER_INFO_TYPE_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = iInfoTypeId;

            String sxQuery = "PR_DELETE_T_CUSTOMER_INFO";

            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
        }

        public static int InsertCustomerBriefDesc(int iCustomerId, String sxValue)
        {
            DBLib dbl = new DBLib();
            int iReturn = InsertCustomerBriefDesc(dbl, iCustomerId, sxValue);
            
            return iReturn;
        }
        public static int InsertCustomerBriefDesc(DBLib dbl, int iCustomerId, String sxValue)
        {
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "out_new_id";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Direction = ParameterDirection.Output;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_VALUE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = sxValue;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CUSTOMER_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = iCustomerId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CREATE_BY";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

            String sxQuery = "PR_INSERT_T_CUST_BRIEF_DESC";

            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());

            return Convert.ToInt32(lsParams[0].Value);
        }

        public static void UpdateCustomerBriefDesc(int iCustomerId, String sxValue)
        {
            DBLib dbl = new DBLib();
            UpdateCustomerBriefDesc(dbl, iCustomerId, sxValue);
            
        }
        public static void UpdateCustomerBriefDesc(DBLib dbl, int iCustomerBriefDescId, String sxValue)
        {
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_VALUE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = sxValue;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = iCustomerBriefDescId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_UPDATE_BY";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

            String sxQuery =
                @"
                update 
                    T_CUSTOMER_BRIEF_DESC
                set
                    VALUE = :in_VALUE,
                    UPDATE_BY = :in_UPDATE_BY,
                    UPDATE_DATE = SysDate
                where
                    T_CUSTOMER_BRIEF_DESC_ID = :prmID
                ";

            dbl.ExecuteNonQuery(sxQuery, lsParams.ToArray());
        }

        public static void DeleteCustomerBriefDesc(int iCustomerBriefDescId)
        {
            DBLib dbl = new DBLib();
            DeleteCustomerBriefDesc(dbl, iCustomerBriefDescId);
            
        }
        public static void DeleteCustomerBriefDesc(DBLib dbl, int iCustomerBriefDescId)
        {
            OracleParameter param = new OracleParameter();
            param.ParameterName = "prmID";
            param.OracleDbType = OracleDbType.Number;
            param.Value = iCustomerBriefDescId;

            String sxQuery =
                @"
                delete from 
                    T_CUSTOMER_BRIEF_DESC
                where
                    T_CUSTOMER_BRIEF_DESC_ID = :prmID
                ";

            dbl.ExecuteNonQuery(sxQuery, param);
        }

    }
}